package gameSite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.jcraft.jsch.*;

public class RemoteConnection {
	// JDBC driver name and database URL
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static  String DB_URL = "jdbc:mysql://clipper.encs.concordia.ca:3306/soen387b";
	  // static final String DB_URL = "jdbc:mysql://localhost:3306/Comp6411";

	 //  Database credentials
	   static final String USER = "soen387b";
	   static final String PASS = "f32k64";
//	   static final String USER = "root";
//	   static final String PASS = "MyApp";
	   static int lport, rport;
	   static String rhost;
	   
	   static Connection conn = null;
	  
	   
	   public static void secureTunnel(){
	        String user = "j_pandya";
	        String password = ">zly>3Zk";
	        String host = "login.encs.concordia.ca";
	        int port=22;
	        try
	            {
	            JSch jsch = new JSch();
	            Session session = jsch.getSession(user, host, port);
	            lport = 3308;
	            rhost = "clipper.encs.concordia.ca";
	            rport = 3306;
	            session.setPassword(password);
	            session.setConfig("StrictHostKeyChecking", "no");
	            System.out.println("Establishing Connection...");
	            session.connect();
	            int assinged_port=session.setPortForwardingL(lport, rhost, rport);
	            System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
	            }
	        catch(Exception e){System.err.print(e);}
	    }
	   
	   public static Connection databaseConnection() {
		   try{
			   secureTunnel();
		      //STEP 2: Register JDBC driver
		      Class.forName("com.mysql.jdbc.Driver");
		      DB_URL = "jdbc:mysql://localhost:"+lport+"/soen387b";
		      //STEP 3: Open a connection
		      System.out.println("Connecting to database...");
		      conn = DriverManager.getConnection(DB_URL,USER,PASS);	      
		      return conn;
		   }catch(Exception e){
		      //Handle errors for Class.forName
			   System.out.println("Invalid Connection");
		      e.printStackTrace();
		   }
		   return null;
	   }
	   
	   public static Connection getConnection(){
		  try{
			  if(conn == null || conn.isClosed())
				  return databaseConnection();
		  }catch(Exception e){
		      //Handle errors for Class.forName
			   System.out.println("Invalid Connection");
		      e.printStackTrace();
		   }
		  return conn;
	   }
	   
	   public static void closeConnection(){
		   if(conn!= null)
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	   }
	   
	   public static void main(String[] args) {
		   Connection conn = null;
		   Statement stmt = null;
		   try{
			   secureTunnel();
		      //STEP 2: Register JDBC driver
		      Class.forName("com.mysql.jdbc.Driver");
		      DB_URL = "jdbc:mysql://localhost:"+lport+"/soen387b";
		      //STEP 3: Open a connection
		      System.out.println("Connecting to database...");
		      conn = DriverManager.getConnection(DB_URL,USER,PASS);

		      //STEP 4: Execute a query
		      System.out.println("Creating statement...");
		      stmt = conn.createStatement();
		      String sql;
		      sql = "SELECT BookID, BookName FROM books";
		      ResultSet rs = stmt.executeQuery(sql);

		      //STEP 5: Extract data from result set
		      while(rs.next()){
		         //Retrieve by column name
		         int id  = rs.getInt("BookID");
		         String BookName = rs.getString("BookName");

		         //Display values
		         System.out.print("ID: " + id);
		         System.out.println(", BookName: " + BookName);
		      }
		      //STEP 6: Clean-up environment
		      rs.close();
		      stmt.close();
		      conn.close();
		   }catch(SQLException se){
		      //Handle errors for JDBC
		      se.printStackTrace();
		   }catch(Exception e){
		      //Handle errors for Class.forName
		      e.printStackTrace();
		   }finally{
		      //finally block used to close resources
		      try{
		         if(stmt!=null)
		            stmt.close();
		      }catch(SQLException se2){
		      }// nothing we can do
		      try{
		         if(conn!=null)
		            conn.close();
		      }catch(SQLException se){
		         se.printStackTrace();
		      }//end finally try
		   }//end try
		   System.out.println("Goodbye!");
		}//end main
}
